﻿Imports System.Timers
Imports System.Data.SqlClient
Imports System.Data
Imports System.Data.Odbc
Imports System.Xml

Partial Class Timer1
    Inherits System.Web.UI.Page

    Dim sConString As String = "DRIVER={MySQL ODBC 5.1 Driver}; SERVER=bluetonemedia.dyndns.org; DATABASE=asteriskcdrdb; UID=tbm; PASSWORD=tbm; OPTION=3;PORT=3306"
    Dim oConnection As OdbcConnection = New OdbcConnection(sConString)
    Dim number As String
    Dim cut As Integer
    Dim conn As New SqlConnection("Data Source=sql.bluetonemedia.com;Initial Catalog=BlueToneMediaDB;Persist Security Info=True;User ID=BTMWiz;Password=esquel4ma@gic;Failover Partner=sqlfailover.bluetonemedia.com")
    Dim Qry As String
    Dim dv As New DataView
    Dim dv2 As New DataView


    Private Sub createNode(ByVal src As String, ByVal caller As String, ByVal dst As String, ByVal crm As String, ByVal dt As String, ByVal writer As XmlTextWriter)
        writer.WriteStartElement("Call")
        writer.WriteStartElement("source")
        writer.WriteString(src)
        writer.WriteEndElement()
        writer.WriteStartElement("caller")
        writer.WriteString(caller)
        writer.WriteEndElement()
        writer.WriteStartElement("to")
        writer.WriteString(dst)
        writer.WriteEndElement()
        writer.WriteStartElement("CRM")
        writer.WriteString(crm)
        writer.WriteEndElement()
        writer.WriteStartElement("dateandtime")
        writer.WriteString(dt)
        writer.WriteEndElement()
        writer.WriteEndElement()
    End Sub

    Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
		Dim dateH As String
		Dim dateD as String
        Dim hour As Integer

        dateH = DateTime.Now.ToString("HH")
		dateD = DateTime.Now.ToString("dddd")
        hour = CInt(dateH)
		
		If dateD <> "Saturday" And dateD <> "Sunday" Then
        	If hour > 8 And hour < 18 Then
        		GetData()
			End If
		End If
		Label1.text = "Done?"
		
    End Sub
    Protected Sub Timer1_Tick(ByVal sender As Object, ByVal e As EventArgs)
		Dim dateH As String
		Dim dateD as String
        Dim hour As Integer

        dateH = DateTime.Now.ToString("HH")
		dateD = DateTime.Now.ToString("dddd")
        hour = CInt(dateH)

		If dateD <> "Saturday" And dateD <> "Sunday" Then
        	If hour > 8 And hour < 18 Then
        		GetData()
			End If
		End If
		
    End Sub

    Public Sub GetData()
        Dim sSQL As String = "SELECT src, clid, dst, calldate FROM cdr WHERE src != '9107952280' ORDER BY calldate DESC LIMIT 100"
        Dim oDataAdapter As OdbcDataAdapter = New OdbcDataAdapter(sSQL, oConnection)
        Dim oDataSet As DataSet = New DataSet()
        oDataAdapter.Fill(oDataSet)
        oDataGrid.DataSource = oDataSet
        oDataGrid.DataBind()

        dv.Table = oDataSet.Tables(0)

        Dim i As Integer
        Dim cut As Integer

        cut = 0

        Dim dub As String

        dub = ""
        number = ""


        Dim writer As New XmlTextWriter(Server.MapPath("") & "/product.xml", System.Text.Encoding.UTF8)
        writer.WriteStartDocument(True)
        writer.Formatting = Formatting.Indented
        writer.Indentation = 2
        writer.WriteStartElement("Table")

        For i = 0 To 99
            
            If Server.HtmlDecode(dv(i)("src")).Length > 3 And cut < 10 And Server.HtmlDecode(dv(i)("src")) <> dub Then

                Dim phone As String

                phone = ""

                phone = Server.HtmlDecode(dv(i)("src"))

                Dim area, pre, ends As String

                area = ""
                pre = ""
                ends = ""

                If Left(phone, 1) = "+" Then
                    area = Mid(phone, 3, 3)
                    pre = Mid(phone, 6, 3)
                    ends = Right(phone, 4)
                Else
                    area = Mid(phone, 1, 3)
                    pre = Mid(phone, 5, 3)
                    ends = Right(phone, 4)
                End If

                'Qry = "SELECT name FROM  Companys WHERE Phone LIKE '%" & area & "%' AND Phone LIKE '%" & pre & "%' AND Phone LIKE '%" & ends & "%'"
				Qry = "SELECT * FROM Contacts as con, Companys as com WHERE (Mobile LIKE '%" & area & "%' Or Phone LIKE '%" & area & "%') AND (Mobile LIKE '%" & pre & "%' Or Phone LIKE '%" & pre & "%') AND (Mobile LIKE '%" & ends & "%' or Phone LIKE '%" & ends & "%') and con.CompanyID = com.ID "
				
                conn.Open()
                Dim Sample As New DataSet
                Dim Sql As New SqlDataAdapter(Qry, conn)
                Sql.Fill(Sample)
                dv2.Table = Sample.Tables(0)

                'Sample.WriteXml(Server.MapPath("") & "/callxml2.xml")
                'Response.write(Server.HtmlDecode(dv2(0)("count")) & "<Br/>")

                conn.Close()

                If Sample.Tables(0).Rows().Count = 0 Then
                    number = number & Server.HtmlDecode(dv(i)("src")) & "`" & Server.HtmlDecode(dv(i)("clid")) & "`" & Server.HtmlDecode(dv(i)("dst")) & "`0`" & Server.HtmlDecode(Format(dv(i)("calldate"), "ddd h:m tt")) & "~"
                    createNode(Server.HtmlDecode(dv(i)("src")), Server.HtmlDecode(dv(i)("clid")), Server.HtmlDecode(dv(i)("dst")), 0, Server.HtmlDecode(Format(dv(i)("calldate"), "ddd h:m tt")), writer)
                Else
                    'number = number & Server.HtmlDecode(dv(i)("src")) & "`" & Server.HtmlDecode(dv2(0)("Name")) & "`" & Server.HtmlDecode(dv(i)("dst")) & "`1`" & Server.HtmlDecode(Format(dv(i)("calldate"), "ddd h:m tt")) & "~"
                    'createNode(Server.HtmlDecode(dv(i)("src")), Server.HtmlDecode(dv2(0)("Name")), Server.HtmlDecode(dv(i)("dst")), 1, Server.HtmlDecode(Format(dv(i)("calldate"), "ddd h:m tt")), writer)
					If Server.HtmlDecode(dv2(0)("Name")) = "" Then
                        number = number & Server.HtmlDecode(dv(i)("src")) & "`" & Server.HtmlDecode(dv2(0)("FName")) & " " & Server.HtmlDecode(dv2(0)("LName")) & "`" & Server.HtmlDecode(dv(i)("dst")) & "`1`" & Server.HtmlDecode(Format(dv(i)("calldate"), "ddd h:m tt")) & "~"
                        createNode(Server.HtmlDecode(dv(i)("src")), Server.HtmlDecode(dv2(0)("FName")) & " " & Server.HtmlDecode(dv2(0)("LName")), Server.HtmlDecode(dv(i)("dst")), 1, Server.HtmlDecode(Format(dv(i)("calldate"), "ddd h:m tt")), writer)
                    Else
                        number = number & Server.HtmlDecode(dv(i)("src")) & "`" & Server.HtmlDecode(dv2(0)("Name")) & "`" & Server.HtmlDecode(dv(i)("dst")) & "`1`" & Server.HtmlDecode(Format(dv(i)("calldate"), "ddd h:m tt")) & "~"
                        createNode(Server.HtmlDecode(dv(i)("src")), Server.HtmlDecode(dv2(0)("Name")), Server.HtmlDecode(dv(i)("dst")), 1, Server.HtmlDecode(Format(dv(i)("calldate"), "ddd h:m tt")), writer)
                    End If
                End If
                'createNode("+19103868470", Server.HtmlDecode(dv(i)("clid")), 506, 1, "2/24/2012 9:15:04 AM", writer)
                cut = cut + 1
                dub = Server.HtmlDecode(dv(i)("src"))

                'createNode(Server.HtmlDecode(dv(i)("src")), Server.HtmlDecode(dv(i)("clid")), Server.HtmlDecode(dv(i)("dst")), 1, Server.HtmlDecode(Format(dv(i)("calldate"), "ddd h:m tt")), writer)
                'createNode(Server.HtmlDecode(dv(i)("src")), Server.HtmlDecode(dv(i)("clid")), Server.HtmlDecode(dv(i)("dst")), 0, Server.HtmlDecode(Format(dv(i)("calldate"), "ddd h:m tt")), writer)

                
            ElseIf cut = 10 Then
                Exit For
            End If

            

        Next
        writer.WriteEndElement()
        writer.WriteEndDocument()
        writer.Close()
        

        Label1.Text = number
    End Sub

    Protected Sub ScriptManager1_AsyncPostBackError(ByVal sender As Object, ByVal e As System.Web.UI.AsyncPostBackErrorEventArgs)
        If (e.Exception.Data("ExtraInfo") <> Nothing) Then
            ScriptManager1.AsyncPostBackErrorMessage = _
               e.Exception.Message & _
               e.Exception.Data("ExtraInfo").ToString()
        Else
            ScriptManager1.AsyncPostBackErrorMessage = _
               "An unspecified error occurred."
        End If
    End Sub
End Class
